CREATE PROC LOADLOAIKICHDIEN
AS
	SELECT *
	FROM LOAIKICHDIEN
GO

CREATE PROC THEMLOAIKICHDIEN
	@TenLoai nvarchar(50)
AS
	INSERT INTO LOAIKICHDIEN VALUES(@TenLoai)
GO

CREATE PROC SUALOAIKICHDIEN
	@MaLoai int,
	@TenLoai nvarchar(50)
AS
	UPDATE LOAIKICHDIEN
	SET TenLoai = @TenLoai
	WHERE MaLoai = @MaLoai
GO

CREATE PROC XOALOAIKICHDIEN
	@MaLoai int
AS
	DELETE LOAIKICHDIEN
	WHERE MaLoai = @MaLoai
GO

CREATE PROC KTLOAIKICHDIEN
	@TenLoai nvarchar(50),
	@out int out
AS
	IF EXISTS (SELECT * FROM LOAIKICHDIEN WHERE TenLoai = @TenLoai)
		SET @out = 1
	ELSE
		SET @out = 0
GO

CREATE PROC KTMALOAIKICHDIEN
	@MaLoai int,
	@out int out
AS
	IF EXISTS (SELECT * FROM KICHDIEN WHERE MaLoai = @MaLoai)
		SET @out = 1
	ELSE
		SET @out = 0
GO

CREATE PROC LOADBOKICHDIEN
AS
	SELECT STT, NgayNhap,MaTB,TenTB,CSKhaDung,DonGia, lkd.TenLoai
	--SELECT *
	FROM KICHDIEN kd, LOAIKICHDIEN lkd
	WHERE kd.MaLoai = lkd.MaLoai
GO

CREATE PROC THEMBOKICHDIEN
	@NgayNhap datetime,
	@MaTB char(20),
	@TenTB nvarchar(50),
	@Vol decimal,
	@GhiChu nvarchar(50),
	@CSKhaDung int,
	@DonGia int,
	@MaLoai int
AS
	INSERT INTO KICHDIEN VALUES(@NgayNhap,@MaTB,@TenTB,@Vol,@GhiChu,@CSKhaDung,@DonGia,@MaLoai)
GO

CREATE PROC SUABOKICHDIEN
	@STT int,
	@NgayNhap datetime,
	@MaTB char(20),
	@TenTB nvarchar(50),
	--@Vol decimal,
	--@GhiChu nvarchar(50),
	@CSKhaDung int,
	@DonGia int,
	@MaLoai int
AS
	UPDATE KICHDIEN
	SET NgayNhap=@NgayNhap,MaTB=@MaTB,TenTB=@TenTB,Vol=0,GhiChu=N'',CSKhaDung=@CSKhaDung,DonGia=@DonGia,MaLoai=@MaLoai
	WHERE STT = @STT
GO

CREATE PROC XOABOKICHDIEN
	@STT int
AS
	DELETE FROM KICHDIEN
	WHERE STT = @STT
GO

CREATE PROC KIEMTRAMATBOKD
	@MaTB int,
	@out int out
AS
	IF EXISTS (SELECT * FROM KICHDIEN WHERE MaTB = @MaTB) 
		SET @out = 1
	ELSE
		SET @out = 0
GO

--DROP PROC KIEMTRAMATBOKD
--DROP PROC THEMLOAIKICHDIEN
--DROP PROC KTLOAIKICHDIEN
--DROP PROC LOADLOAIKICHDIEN

--DROP PROC LOADBOKICHDIEN
--DROP PROC THEMBOKICHDIEN
--DROP PROC SUABOKICHDIEN
--DROP PROC XOABOKICHDIEN
--------------------------------------------------------------------------

CREATE PROC LOADLOAITHIETBIDIEN
AS
	SELECT *
	FROM LOAITBDIEN
GO

CREATE PROC THEMLOAITHIETBIDIEN
	@TenLoai nvarchar(50)
AS
	INSERT INTO LOAITBDIEN VALUES(@TenLoai)
GO

CREATE PROC SUALOAITHIETBIDIEN
	@MaLoai int,
	@TenLoai nvarchar(50)
AS
	UPDATE LOAITBDIEN
	SET TenLoai = @TenLoai
	WHERE MaLoai = @MaLoai
GO

CREATE PROC XOALOAITHIETBIDIEN
	@MaLoai int
AS
	DELETE LOAITBDIEN
	WHERE MaLoai = @MaLoai
GO

CREATE PROC KTLOAITBDIEN
	@TenLoai nvarchar(50),
	@out int out
AS
	IF EXISTS (SELECT * FROM LOAITBDIEN WHERE TenLoai = @TenLoai)
		SET @out = 1
	ELSE
		SET @out = 0
GO

CREATE PROC KTMALOAITBDIEN
	@MaLoai int,
	@out int out
AS
	IF EXISTS (SELECT * FROM THIETBIDIEN WHERE MaLoai = @MaLoai)
		SET @out = 1
	ELSE
		SET @out = 0
GO

CREATE PROC LOADTHIETBIDIEN
AS
	SELECT STT,NgayNhap,MaTB,TenTB,CSKhaDung,CSHaoHut,HSDeBa,DonGia,ltb.TenLoai
	--SELECT *
	FROM THIETBIDIEN tbd, LOAITBDIEN ltb
	WHERE tbd.MaLoai = ltb.MaLoai
GO

CREATE PROC THEMTHIETBIDIEN
	@NgayNhap datetime,
	@MaTB char(20),
	@TenTB nvarchar(50),
	--@Vol decimal,
	--@Amp decimal,
	@GhiChu nvarchar(50),
	@CSKhaDung int,
	@CSHaoHut int,
	@HSDeBa int,
	@DonGia int,
	@MaLoai int
AS
	--INSERT INTO THIETBIDIEN VALUES(@NgayNhap,@MaTB,@TenTB,@Vol,@Amp,@GhiChu,@CSKhaDung,@CSHaoHut,@HSDeBa,@DonGia,@MaLoai)
	INSERT INTO THIETBIDIEN VALUES(@NgayNhap,@MaTB,@TenTB,0,0,N'',@CSKhaDung,@CSHaoHut,@HSDeBa,@DonGia,@MaLoai)
GO

CREATE PROC SUATHIETBIDIEN
	@STT int,
	@NgayNhap datetime,
	@MaTB char(20),
	@TenTB nvarchar(50),
	--@Vol decimal,
	--@Amp decimal,
	--@GhiChu nvarchar(50),
	@CSKhaDung int,
	@CSHaoHut int,
	@HSDeBa int,
	@DonGia int,
	@MaLoai int
AS
	UPDATE THIETBIDIEN
	SET NgayNhap=@NgayNhap,MaTB=@MaTB,TenTB=@TenTB,GhiChu=null,
	CSKhaDung=@CSKhaDung,CSHaoHut=@CSHaoHut,HSDeBa=@HSDeBa,DonGia=@DonGia,MaLoai=@MaLoai
	WHERE STT = @STT
GO


CREATE PROC XOATHIETBIDIEN
	@STT int
AS
	DELETE FROM THIETBIDIEN
	WHERE STT = @STT
GO

CREATE PROC KIEMTRAMATBDIEN
	@MaTB int,
	@out int out
AS
	IF EXISTS (SELECT * FROM THIETBIDIEN WHERE MaTB = @MaTB)
		SET @out = 1
	ELSE
		SET @out = 0
GO

INSERT INTO LOAITBDIEN VALUES(N'Đèn')
INSERT INTO LOAITBDIEN VALUES(N'Quạt')
INSERT INTO LOAITBDIEN VALUES(N'TiVi')
INSERT INTO LOAITBDIEN VALUES(N'Tủ lạnh')
INSERT INTO LOAITBDIEN VALUES(N'Laptop')
INSERT INTO LOAITBDIEN VALUES(N'Máy giặt')
INSERT INTO LOAITBDIEN VALUES(N'Máy lạnh')
INSERT INTO LOAITBDIEN VALUES(N'Máy tính')

--INSERT INTO THIETBIDIEN VALUES(@NgayNhap,@MaTB,@TenTB,@Vol,@Amp,@GhiChu,@CSKhaDung,@CSHaoHut,@HSDeBa,@DonGia,@MaLoai)
INSERT INTO THIETBIDIEN VALUES('6/3/2013',1,N'Đèn neon',0,0,null,5,0,1,15000,1)
INSERT INTO THIETBIDIEN VALUES('6/3/2013',2,N'Đèn compact',0,0,null,5,0,1,18000,1)
INSERT INTO THIETBIDIEN VALUES('6/3/2013',3,N'Box Fan',0,0,null,5,0,1,50000,2)
INSERT INTO THIETBIDIEN VALUES('6/3/2013',4,N'Tivi 21in',0,0,null,5,0,1,1000000,3)
INSERT INTO THIETBIDIEN VALUES('6/3/2013',5,N'Tủ lạnh Toshiba',0,0,null,5,0,1,1500000,4)
INSERT INTO THIETBIDIEN VALUES('6/3/2013',6,N'Laptop Sony',0,0,null,5,0,1,12000000,5)
INSERT INTO THIETBIDIEN VALUES('6/3/2013',7,N'Laptop Dell',0,0,null,5,0,1,11000000,5)
INSERT INTO THIETBIDIEN VALUES('6/3/2013',8,N'Laptop VaiO',0,0,null,5,0,1,11500000,5)
INSERT INTO THIETBIDIEN VALUES('6/3/2013',9,N'Laptop Asus',0,0,null,5,0,1,10000000,5)
INSERT INTO THIETBIDIEN VALUES('6/3/2013',9,N'Laptop Acer',0,0,null,5,0,1,12000000,5)

--declare @TonTai int
--exec KIEMTRAMATBDIEN 1,@TonTai out
--print @TonTai

--DROP PROC KIEMTRAMATBDIEN
--DROP PROC LOADLOAITHIETBIDIEN
--DROP PROC THEMLOAITHIETBIDIEN
--DROP PROC LOADTHIETBIDIEN
--DROP PROC THEMTHIETBIDIEN
--DROP PROC SUATHIETBIDIEN
--DROP PROC XOATHIETBIDIEN

--SELECT * FROM AcQuy
--SELECT * FROM KICHDIEN
--SELECT * FROM KHUVUC
--SELECT * FROM LOAIACQUY
--SELECT * FROM LOAIKICHDIEN
--SELECT * FROM LOAIPIN
--SELECT * FROM LOAITBDIEN
--SELECT * FROM MACHSAC
--SELECT * FROM PIN
--SELECT * FROM THIETBIDIEN

--INSERT INTO LOAIKICHDIEN VALUES(@TenLoaiKichDien)
INSERT INTO LOAIKICHDIEN VALUES(N'Thường')
INSERT INTO LOAIKICHDIEN VALUES(N'Tốt')

--INSERT INTO KICHDIEN VALUES(@NgayNhap,@MaTB,,@TenTB,@Vol,@GhiChu,@CSKhaDung,@DonGia,@MaLoai)
INSERT INTO KICHDIEN VALUES('5/29/2013',1,N'',0,N'',800,900000,1)
INSERT INTO KICHDIEN VALUES('5/29/2013',2,N'',0,N'',600,2800000,2)
INSERT INTO KICHDIEN VALUES('5/29/2013',3,N'',0,N'',1000,3700000,2)
INSERT INTO KICHDIEN VALUES('5/29/2013',4,N'',0,N'',2000,6500000,2)